from flask import Flask, render_template, request, jsonify, redirect
import sqlite3
from book import book_blueprint  # 从book包导入蓝图（依赖__init__.py）

app = Flask(__name__)

DATABASE = 'home.db'

def get_db_connection():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row  # 支持通过列名访问数据
    return conn

def execute_sql_result(sql, *args):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(sql, args)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

def execute_sql_no_result(sql, *args):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute(sql, args)
    conn.commit()
    cursor.close()
    conn.close()

# 注册蓝图
app.register_blueprint(book_blueprint, url_prefix='/book')

@app.route('/')
def hello_world():
    return render_template('book_index.html')

@app.route('/login')
def login_page():
    return render_template('login.html')

@app.route('/dologin', methods=['GET', 'POST'])
def doLogin():
    username = request.form.get('username')
    password = request.form.get('password')

    conn = get_db_connection()
    cursor = conn.cursor()
    sql = 'SELECT * FROM users WHERE username=? AND password=?'
    cursor.execute(sql, (username, password))
    result = cursor.fetchone()
    cursor.close()
    conn.close()

    if result:
        return render_template('index.html')
    else:
        return render_template('login.html', error='用户名密码错误')

@app.route('/users')
def users_page():
    conn = get_db_connection()
    cursor = conn.cursor()
    sql = 'SELECT * FROM users'
    cursor.execute(sql)
    result = cursor.fetchall()
    cursor.close()
    conn.close()

    return render_template('users.html', users=result)

@app.route('/deleteuser/<int:user_id>')
def delete_user(user_id):
    conn = get_db_connection()
    cursor = conn.cursor()

    sql = 'DELETE FROM users WHERE user_id=?'
    cursor.execute(sql, (user_id,))
    conn.commit()

    sql = 'SELECT * FROM users'
    cursor.execute(sql)
    result = cursor.fetchall()

    cursor.close()
    conn.close()
    return render_template('users.html', users=result)

@app.route('/adduser', methods=['POST'])
def ajax_add():
    json_data = request.get_json()
    username = json_data['username']
    password = json_data['password']
    sql = 'INSERT INTO users(username, password) VALUES (?, ?)'
    execute_sql_no_result(sql, username, password)
    return jsonify({'status': 'ok'})

@app.route('/edituser', methods=['POST'])
def ajax_edit():
    json_data = request.get_json()
    username = json_data['username']
    password = json_data['password']
    user_id = json_data['user_id']
    sql = 'UPDATE users SET username=?, password=? WHERE user_id=?'
    execute_sql_no_result(sql, username, password, user_id)
    return jsonify({'status': 'ok'})

if __name__ == '__main__':
    app.run(debug=True)
